﻿using System;
using Finance.Data.Entities;
using Finance.Properties;
using TodoSoft.Data.Migration;

namespace Finance
{
	[MigrationComponent("{708E03BC-662F-4C6C-B921-F242C60EE95A}", "Finance database")]
	internal class FinanceDataBaseMigration : Migration
	{
		[Changeset(1, "Initial create tables")]
		public void CreateTables()
		{
			var contractorTable = "contractor";
			DataBase.CreateTable(contractorTable);
			DataBase.AlterTableAddColumn(contractorTable, "description", "ntext");
			DataBase.AlterTableAddColumn(contractorTable, "name", typeof(string), 100);
			DataBase.AlterTableAddColumn(contractorTable, "createdOn", typeof(DateTime));

			var documentTypeTable = "documenttype";
			DataBase.CreateTable(documentTypeTable);
			DataBase.AlterTableAddColumn(documentTypeTable, "name", typeof(string), 50);
			DataBase.AlterTableAddColumn(documentTypeTable, "code", typeof(int));
			DataBase.AlterTableAddColumn(documentTypeTable, "sign", typeof(int));

			var documentSubTypeTable = "documentsubtype";
			DataBase.CreateTable(documentSubTypeTable);
			DataBase.AlterTableAddColumn(documentSubTypeTable, "documenttypeid", typeof(Guid));
			DataBase.AlterTableAddColumn(documentSubTypeTable, "name", typeof(string), 50);
            
			var documentTable = "document";
			DataBase.CreateTable(documentTable);
			DataBase.AlterTableAddColumn(documentTable, "docdatetime", typeof(DateTime));
			DataBase.AlterTableAddColumn(documentTable, "description", "ntext");
			DataBase.AlterTableAddColumn(documentTable, "amount", typeof(decimal));
			DataBase.AlterTableAddColumn(documentTable, "documenttypeid", typeof(Guid));
			DataBase.AlterTableAddColumn(documentTable, "documentsubtypeid", typeof(Guid));
			DataBase.AlterTableAddColumn(documentTable, "contractorid", typeof(Guid));

			var noteTable = "note";
			DataBase.CreateTable(noteTable);
			DataBase.AlterTableAddColumn(noteTable, "createdon", typeof(DateTime));
			DataBase.AlterTableAddColumn(noteTable, "text", "ntext");

			DataBase.Insert(
				documentTypeTable,
				new [] {documentTypeTable + "id", "name", "code", "sign"},
				new object[]  {new Guid("{DF3F3BDA-EFD8-3240-B56F-7B612725F4D2}"), Resources.DocumentType_Income, 1, 1});

			DataBase.Insert(
				documentTypeTable,
				new[] { documentTypeTable + "id", "name", "code", "sign" },
				new object[] { new Guid("{CFFDD87B-F7F5-B770-6866-2C1440104234}"), Resources.DocumentType_Expense, 2, -1 });

			DataBase.Insert(
				documentTypeTable,
				new[] { documentTypeTable + "id", "name", "code", "sign" },
				new object[] { new Guid("{156F56F9-E722-2B8E-11A4-706B3C9334D5}"), Resources.DocumentType_IncomeCreditBack, 3, 1 });

			DataBase.Insert(
				documentTypeTable,
				new[] { documentTypeTable + "id", "name", "code", "sign" },
				new object[] { new Guid("{74D3FA9C-3D82-2C25-965F-30188885BE9A}"), Resources.DocumentType_IncomeCreditGet, 4, 1 });

			DataBase.Insert(
				documentTypeTable,
				new[] { documentTypeTable + "id", "name", "code", "sign" },
				new object[] { new Guid("{A6D78AD5-6F43-2D68-2539-43D3B89A67DF}"), Resources.DocumentType_ExpenseCreditBack, 5, -1 });

			DataBase.Insert(
				documentTypeTable,
				new[] { documentTypeTable + "id", "name", "code", "sign" },
				new object[] { new Guid("{9F5889FE-BEFA-17A6-B04E-258CEBBE5273}"), Resources.DocumentType_ExpenseCreditGive, 6, -1 });
		}

		[Changeset(2, "Document, product table")]
		public void CreateDocumentProductTable()
		{
			const string productTableName = "product";
			DataBase.CreateTable(productTableName);
			DataBase.AlterTableAddColumn(productTableName, "name", typeof(string), 64);
			const string documentProductTableName = "documentproduct";
			DataBase.CreateTable(documentProductTableName);
			DataBase.AlterTableAddColumn(documentProductTableName, "productid", typeof(Guid));
			DataBase.AlterTableAddColumn(documentProductTableName, "amount", typeof(decimal));
			DataBase.AlterTableAddColumn(documentProductTableName, "price", typeof(decimal));
			DataBase.AlterTableAddColumn(documentProductTableName, "totalPrice", typeof(decimal));
			DataBase.AlterTableAddColumn(documentProductTableName, "documentsubtype", typeof(Guid));
			DataBase.AlterTableAddColumn(documentProductTableName, "documentid", typeof(Guid));
		}

		[Changeset(3, "Add discount column")]
		public void DocumentProductAddDiscountColumn()
		{
			DataBase.AlterTableAddColumn("documentproduct", "discount", typeof(decimal));
		}

		[Changeset(4, "Add indexes")]
		public void AddIndexes()
		{
			DataBase.AlterTableAddIndex("document", "contractorid");
			DataBase.AlterTableAddIndex("documentproduct", "productid");
		}

		[Changeset(5, "Index for subtype of document")]
		public void AddIndexes2()
		{
			DataBase.AlterTableAddIndex("document", "documentsubtypeid");
		}

		[Changeset(6, "Index for docdatetime of document")]
		public void AddDateIndexes()
		{
			DataBase.AlterTableAddIndex("document", "docdatetime");
		}

		[Changeset(7, "One more index for document")]
		public void AddDocTypeIndexe()
		{
			DataBase.AlterTableAddIndex("document", "documenttypeid");
		}


		[Changeset(8, "More indexes")]
		public void AddMoreIndexes()
		{
			DataBase.AlterTableAddPrimaryKey("documentsubtype", "documentsubtypeid");
			DataBase.AlterTableAddPrimaryKey("documenttype", "documenttypeid");
			DataBase.AlterTableAddPrimaryKey("document", "documentid");
			DataBase.AlterTableAddPrimaryKey("product", "productid");
		}

		[Changeset(9, "More indexes2")]
		public void AddMoreIndexes2()
		{
			DataBase.AlterTableAddIndex("documentproduct", "documentid");
		}

		[Changeset(10, "Remove documenttype")]
		public void RemoveDocumentTypes()
		{
			DataBase.AlterTableAddColumn("document", "typecode", typeof(int));
			DataBase.AlterTableAddColumn("documentsubtype", "typecode", typeof(int));
			using (var reader = DataBase.ExecuteReader("select * from documenttype"))
			{
				while (reader.Read())
				{
					var typeCode = (int)reader["code"];
					var typeid = (Guid) reader["documenttypeid"];

					DataBase.ExecuteNonQuery(
						"update document set typecode = @p0 where documenttypeid = @p1",
					    typeCode, typeid);

					DataBase.ExecuteNonQuery(
						"update documentsubtype set typecode = @p0 where documenttypeid = @p1",
						typeCode, typeid);
				}
			}

			DataBase.AlterTableDropColumn("documentsubtype", "documenttypeid");
			DataBase.AlterTableDropIndex("document", "documenttypeid");
			DataBase.AlterTableDropColumn("document", "documenttypeid");
			DataBase.DropTable("documenttype");
		}

		[Changeset(11, "Create debt documents tables")]
		public void NewDebtDocuments()
		{
			// Create new tables
			const string debtDoc = "debtdocument";
			DataBase.CreateTable(debtDoc, "id");
			DataBase.AlterTableAddPrimaryKey(debtDoc, "id");
			DataBase.AlterTableAddColumn(debtDoc, "docdatetime", typeof(DateTime));
			DataBase.AlterTableAddColumn(debtDoc, "description", "ntext");
			DataBase.AlterTableAddColumn(debtDoc, "amount", typeof(decimal));
			DataBase.AlterTableAddColumn(debtDoc, "typecode", typeof(int));
			DataBase.AlterTableAddColumn(debtDoc, "documentsubtypeid", typeof(Guid));
			DataBase.AlterTableAddColumn(debtDoc, "contractorid", typeof(Guid));
			DataBase.AlterTableAddColumn(debtDoc, "rate", typeof(decimal));

			const string repayDebtDoc = "repaydebtdocument";
			DataBase.CreateTable(repayDebtDoc, "id");
			DataBase.AlterTableAddPrimaryKey(repayDebtDoc, "id");
			DataBase.AlterTableAddColumn(repayDebtDoc, "docdatetime", typeof(DateTime));
			DataBase.AlterTableAddColumn(repayDebtDoc, "description", "ntext");
			DataBase.AlterTableAddColumn(repayDebtDoc, "amount", typeof(decimal));
			DataBase.AlterTableAddColumn(repayDebtDoc, "typecode", typeof(int));
			DataBase.AlterTableAddColumn(repayDebtDoc, "documentsubtypeid", typeof(Guid));
			DataBase.AlterTableAddColumn(repayDebtDoc, "contractorid", typeof(Guid));
			DataBase.AlterTableAddColumn(repayDebtDoc, "debtdocumentid", typeof(Guid));

			// Copy data
			DataBase.ExecuteNonQuery(
				"insert into debtdocument (id, docdatetime, description, amount, typecode, documentsubtypeid, contractorid, rate) " +
				"select documentid, docdatetime,description, amount, typecode, documentsubtypeid, contractorid, 0 from document " +
				"where document.typecode = @p0 or document.typecode = @p1",
				(int)DocumentType.DohodZaim, (int)DocumentType.RashodZaim);

			DataBase.ExecuteNonQuery(
				"insert into repaydebtdocument (id, docdatetime, description, amount, typecode, documentsubtypeid, contractorid) " +
				"select documentid, docdatetime, description, amount, typecode, documentsubtypeid, contractorid from document where " +
				"document.typecode = @p0 or document.typecode = @p1",
				(int)DocumentType.DohodZaimBack, (int)DocumentType.RashodZaimBack);

			// Clean document table
			DataBase.ExecuteNonQuery(
				"delete from document where typecode <> @p0 and typecode <> @p1",
				(int)DocumentType.Rashod, (int)DocumentType.Dohod);
		}

		[Changeset(12, "Add fields to repay debt documents table")]
		public void UpdateRepayDebtDocumentsTable()
		{
			const string repayDebtDoc = "repaydebtdocument";
			DataBase.AlterTableAddColumn(repayDebtDoc, "debtamount", typeof(decimal));
			DataBase.AlterTableAddColumn(repayDebtDoc, "rateamount", typeof(decimal));
		}

		[Changeset(13, "Update debt amount")]
		public void UpdateRepayDebtDocumentsAmount()
		{
			DataBase.ExecuteNonQuery("update repaydebtdocument set debtamount = amount, rateamount = 0");
		}

	}
}
